CREATE, DROP, and INSERT Table

CREATE TABLE#

Creating a basic table involves naming the table and defining its columns and the data type for each column.

The SQL CREATE TABLE statement is used to create a new table.

Syntax#

The basic syntax of the CREATE TABLE statement is as follows:

CREATE TABLE table_name(

   column1 datatype,

   column2 datatype,

   column3 datatype,

   .....

   columnN datatype,

   PRIMARY KEY(one or more columns)

);

CREATE TABLE is the keyword telling the database system what you want to do. The unique name or identifier for the table follows the CREATE TABLE statement.

Then, in brackets, comes the list defining each column in the table and what data type it is.

Example#

The following code block is an example which creates a CUSTOMERS table with ID as a primary key and NOT NULL is the constraint showing that these fields cannot be NULL while creating records in this table:

CREATE TABLE CUSTOMERS(

    ID           INT                                 NOT NULL,

    NAME    VARCHAR (20)             NOT NULL,

    AGE        INT                                NOT NULL,

    ADDRESS     CHAR (25),

    SALARY        DECIMAL (18, 2),

    PRIMARY KEY    (ID)

);

You can verify that your table has been created successfully by using the DESC command:

The DESC command in line 10 creates a table that contains information regarding the columns: name, data type, constraints.

Now, you have a CUSTOMERS table available in your database which you can use to store the required information related to customers.

DROP TABLE#

The SQL DROP TABLE statement is used to remove a table definition and all the data, indexes, triggers, constraints and permission specifications for that table.

You should be very careful while using this command because once a table is deleted, all the information available in that table will also be lost forever.

Syntax#

The basic syntax of the DROP TABLE statement is as follows:

 DROP TABLE table_name;

Example#

Now let us delete the CUSTOMERS table we created above:

Now, if we try using the DESC command, we will get the above error which simply states that there is no table called CUSTOMERS in our ri_db database. Thus we have successfully dropped/deleted the CUSTOMERS table.

INSERT INTO#

The SQL INSERT INTO statement is used to add new rows of data to a table in the database.

Syntax#

There are two basic syntaxes of the INSERT INTO statement which are shown below.

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)  

VALUES (value1, value2, value3,...valueN);

Here, column1, column2, column3,…columnN are the names of the columns in the table into which you want to insert the data.

You may not need to specify the column name(s) in the SQL query if you are adding values for all the columns in the table. But make sure the order of the values is the same as the columns in the table.

The SQL INSERT INTO syntax will be as follows:

INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

Example#

The following statements would create six records in the CUSTOMERS table:

The code written from lines 10-26 will create the following table:

Quick quiz!#

In the next lesson, we will see how the SELECT statement is used to retrieve the columns of a table.